<html><body>
<?php

/**********************************************************************
This file processes the user-inputed values from hscontactsurvey.html *
***********************************************************************/

$conn = oci_connect('stran1', 'db4fun', 'oraserver.cs.luc.edu/cs');//connect to database
if (!$conn) 
{
trigger_error("Could not connect to database", E_USER_ERROR);
}//end if

/****************************************************
Assign user-defined values regarding volunteer info *
to local variables in this program
*****************************************************/
$vname = $_POST['name'];
$vgender = $_POST['sex'];
$vborn = $_POST['birthyear'];
$vethnicity = $_POST['ethnicity'];
$vwphone1 = $_POST['workphac'];
$vwphone2 = $_POST['workphexch'];
$vwphone3 = $_POST['workphrest'];
$vwphone4 = $_POST['workphext'];
$vWorkPh = $vwphone1.$vwphone2.$vwphone3.$vwphone4;
$vhphone1 = $_POST['homephac'];
$vhphone2 = $_POST['homephexch'];
$vhphone3 = $_POST['homephrest'];
$vHomePh = $vhphone1.$vhphone2.$vhphone3;
$vcphone1 = $_POST['cellphac'];
$vcphone2 = $_POST['cellphexch'];
$vcphone3 = $_POST['cellphrest'];
$vCellPh = $vcphone1.$vcphone2.$vcphone3;
$vemail = $_POST['email'];
$valum = $_POST['alum'];
$vvisit = $_POST['visit'];

/**************************************************************
Assign user-defined values regarding high school contact info *
to local variables in this program
***************************************************************/
$school = $_POST['highschool'];
$scity = $_POST['scity'];
$szip = $_POST['szip'];
$sQuery = oci_parse($conn, "select scode from schools where sname = :school_bv and scity = :scity_bv and szip = :szip_bv");//retrieve scode from the schools table in the database
//$sQuery = oci_parse($conn, "select scode from schools where sname = :school_bv and scity = :scity_bv");
oci_bind_by_name($sQuery, ":school_bv", $school);
oci_bind_by_name($sQuery, ":scity_bv", $scity);
oci_bind_by_name($sQuery, ":szip_bv", $szip);
oci_execute($sQuery);
$row = oci_fetch_row($sQuery);
$scode = $row[0];//store the scode into a local variable
oci_free_statement($sQuery);
$cname1 = $_POST['cont1name'];
$ctitle1 = $_POST['cont1pos'];
$cphone11 = $_POST['cont1phac'];
$cphone21 = $_POST['cont1phexch'];
$cphone31 = $_POST['cont1phrest'];
$cphone41 = $_POST['cont1phext'];
$cPh1 = $cphone11.$cphone21.$cphone31.$cphone41;
$cemail1 = $_POST['cont1email'];

$cname2 = $_POST['cont2name'];
$ctitle2 = $_POST['cont2pos'];
$cphone12 = $_POST['cont2phac'];
$cphone22 = $_POST['cont2phexch'];
$cphone32 = $_POST['cont2phrest'];
$cphone42 = $_POST['cont2phext'];
$cPh2 = $cphone12.$cphone22.$cphone32.$cphone42;
$cemail2 = $_POST['cont2email'];

$cname3 = $_POST['cont3name'];
$ctitle3 = $_POST['cont3pos'];
$cphone13 = $_POST['cont3phac'];
$cphone23 = $_POST['cont3phexch'];
$cphone33 = $_POST['cont3phrest'];
$cphone43 = $_POST['cont3phext'];
$cPh3 = $cphone13.$cphone23.$cphone33.$cphone43;
$cemail3 = $_POST['cont3email'];

$cname4 = $_POST['cont4name'];
$ctitle4 = $_POST['cont4pos'];
$cphone14 = $_POST['cont4phac'];
$cphone24 = $_POST['cont4phexch'];
$cphone34 = $_POST['cont4phrest'];
$cphone44 = $_POST['cont4phext'];
$cPh4 = $cphone14.$cphone24.$cphone34.$cphone44;
$cemail4 = $_POST['cont4email'];

$cname5 = $_POST['cont5name'];
$ctitle5 = $_POST['cont5pos'];
$cphone15 = $_POST['cont5phac'];
$cphone25 = $_POST['cont5phexch'];
$cphone35 = $_POST['cont5phrest'];
$cphone45 = $_POST['cont5phext'];
$cPh5 = $cphone15.$cphone25.$cphone35.$cphone45;
$cemail5 = $_POST['cont5email'];

$ccomments = $_POST['comments'];

/**********************************************************
Insert user-defined high school contact info into database*
***********************************************************/

$cemailcheck = oci_parse($conn, "select cemail from contacts where cemail = :cemail_bv");
oci_bind_by_name($cemailcheck, ":cemail_bv", $cemail1);
oci_execute($cemailcheck);
$row = oci_fetch_array($cemailcheck);
$numRows = count($row);

if($cemail1 != NULL && $numRows == 1)
{
	oci_free_statement($cemailcheck);
	$cInsert = oci_parse($conn, "insert into contacts values(:cemail_bv, :cname_bv, :cPh_bv, :ctitle_bv, :ccomments_bv, :scode_bv)");
	oci_bind_by_name($cInsert, ":cemail_bv", $cemail1);
	oci_bind_by_name($cInsert, ":cname_bv", $cname1);
	oci_bind_by_name($cInsert, ":cPh_bv", $cPh1);
	oci_bind_by_name($cInsert, ":ctitle_bv", $ctitle1);
	oci_bind_by_name($cInsert, ":ccomments_bv", $ccomments);
	oci_bind_by_name($cInsert, ":scode_bv", $scode);
	oci_execute($cInsert);
	oci_free_statement($cInsert);
}//end if


$cemailcheck = oci_parse($conn, "select * from contacts where cemail = :cemail_bv");
oci_bind_by_name($cemailcheck, ":cemail_bv", $cemail2);
oci_execute($cemailcheck);
$row = oci_fetch_array($cemailcheck);
$numRows = count($row);

if($cemail2 != NULL && $numRows == 1)
{
	oci_free_statement($cemailcheck);
	$cInsert = oci_parse($conn, "insert into contacts values(:cemail_bv, :cname_bv, :cPh_bv, :ctitle_bv, :ccomments_bv, :scode_bv)");
	oci_bind_by_name($cInsert, ":cemail_bv", $cemail2);
	oci_bind_by_name($cInsert, ":cname_bv", $cname2);
	oci_bind_by_name($cInsert, ":cPh_bv", $cPh2);
	oci_bind_by_name($cInsert, ":ctitle_bv", $ctitle2);
	oci_bind_by_name($cInsert, ":ccomments_bv", $ccomments);
	oci_bind_by_name($cInsert, ":scode_bv", $scode);
	oci_execute($cInsert);
	oci_free_statement($cInsert);
}//end if


$cemailcheck = oci_parse($conn, "select * from contacts where cemail = :cemail_bv");
oci_bind_by_name($cemailcheck, ":cemail_bv", $cemail3);
oci_execute($cemailcheck);
$row = oci_fetch_array($cemailcheck);
$numRows = count($row);

if($cemail3 != NULL && $numRows == 1)
{
	oci_free_statement($cemailcheck);
	$cInsert = oci_parse($conn, "insert into contacts values(:cemail_bv, :cname_bv, :cPh_bv, :ctitle_bv, :ccomments_bv, :scode_bv)");
	oci_bind_by_name($cInsert, ":cemail_bv", $cemail3);
	oci_bind_by_name($cInsert, ":cname_bv", $cname3);
	oci_bind_by_name($cInsert, ":cPh_bv", $cPh3);
	oci_bind_by_name($cInsert, ":ctitle_bv", $ctitle3);
	oci_bind_by_name($cInsert, ":ccomments_bv", $ccomments);
	oci_bind_by_name($cInsert, ":scode_bv", $scode);
	oci_execute($cInsert);
	oci_free_statement($cInsert);
}//end if


$cemailcheck = oci_parse($conn, "select * from contacts where cemail = :cemail_bv");
oci_bind_by_name($cemailcheck, ":cemail_bv", $cemail4);
oci_execute($cemailcheck);
$row = oci_fetch_array($cemailcheck);
$numRows = count($row);

if($cemail4 != NULL && $numRows == 1)
{
	oci_free_statement($cemailcheck);
	$cInsert = oci_parse($conn, "insert into contacts values(:cemail_bv, :cname_bv, :cPh_bv, :ctitle_bv, :ccomments_bv, :scode_bv)");
	oci_bind_by_name($cInsert, ":cemail_bv", $cemail4);
	oci_bind_by_name($cInsert, ":cname_bv", $cname4);
	oci_bind_by_name($cInsert, ":cPh_bv", $cPh4);
	oci_bind_by_name($cInsert, ":ctitle_bv", $ctitle4);
	oci_bind_by_name($cInsert, ":ccomments_bv", $ccomments);
	oci_bind_by_name($cInsert, ":scode_bv", $scode);
	oci_execute($cInsert);
	oci_free_statement($cInsert);
}//end if


$cemailcheck = oci_parse($conn, "select * from contacts where cemail = :cemail_bv");
oci_bind_by_name($cemailcheck, ":cemail_bv", $cemail5);
oci_execute($cemailcheck);
$row = oci_fetch_array($cemailcheck);
$numRows = count($row);

if($cemail5 != NULL && $numRows == 1)
{
	oci_free_statement($cemailcheck);
	$cInsert = oci_parse($conn, "insert into contacts values(:cemail_bv, :cname_bv, :cPh_bv, :ctitle_bv, :ccomments_bv, :scode_bv)");
	oci_bind_by_name($cInsert, ":cemail_bv", $cemail5);
	oci_bind_by_name($cInsert, ":cname_bv", $cname5);
	oci_bind_by_name($cInsert, ":cPh_bv", $cPh5);
	oci_bind_by_name($cInsert, ":ctitle_bv", $ctitle5);
	oci_bind_by_name($cInsert, ":ccomments_bv", $ccomments);
	oci_bind_by_name($cInsert, ":scode_bv", $scode);
	oci_execute($cInsert);
	oci_free_statement($cInsert);
}//end if



/************************************************
Insert user-defined volunteer info into database*
*************************************************/

$vemailcheck = oci_parse($conn, "select vemail from volunteer where vemail = :vemail_bv");
oci_bind_by_name($vemailcheck, ":vemail_bv", $vemail);
oci_execute($vemailcheck);
$row = oci_fetch_array($vemailcheck);
$numRows = count($row);

$refercheck = oci_parse($conn, "select vemail from refer where vemail = :vemail_bv and cemail = :cemail_bv");
oci_bind_by_name($refercheck, ":vemail_bv", $vemail);
oci_bind_by_name($refercheck, ":cemail_bv", $cemail1);
oci_execute($refercheck);
$row = oci_fetch_array($refercheck);
$numRows1 = count($row);

if($cemail1 != NULL && $numRows == 1)
{
	oci_free_statement($vemailcheck);
	$vInsert = oci_parse($conn, "insert into volunteer values(:vname_bv, :vgender_bv, :vborn_bv, :vethnicity_bv, :vWorkPh_bv, :vHomePh_bv, :vCellPh_bv, :vemail_bv, :valum_bv, :vvisit_bv)");
	$vInsert2 = oci_parse($conn, "insert into refer values(:cemail_bv, :vemail_bv)");
	oci_bind_by_name($vInsert, ":vname_bv", $vname);
	oci_bind_by_name($vInsert, ":vgender_bv", $vgender);
	oci_bind_by_name($vInsert, ":vborn_bv", $vborn);
	oci_bind_by_name($vInsert, ":vethnicity_bv", $vethnicity);
	oci_bind_by_name($vInsert, ":vWorkPh_bv", $vWorkPh);
	oci_bind_by_name($vInsert, ":vHomePh_bv", $vHomePh);
	oci_bind_by_name($vInsert, ":vCellPh_bv", $vCellPh);
	oci_bind_by_name($vInsert, ":vemail_bv", $vemail);
	oci_bind_by_name($vInsert, ":valum_bv", $valum);
	oci_bind_by_name($vInsert, ":vvisit_bv", $vvisit);
	oci_bind_by_name($vInsert2, ":vemail_bv", $vemail);
	oci_bind_by_name($vInsert2, ":cemail_bv", $cemail1);
	oci_execute($vInsert);
	oci_free_statement($vInsert);
	oci_execute($vInsert2);
	oci_free_statement($vInsert2);
}//end if
elseif($cemail1 != NULL && $numRows > 1 && $numRows1 == 1 )
{
	oci_free_statement($vemailcheck);
	oci_free_statement($refercheck);
	$vInsert = oci_parse($conn, "insert into refer values(:cemail_bv, :vemail_bv)");
	oci_bind_by_name($vInsert, ":vemail_bv", $vemail);
	oci_bind_by_name($vInsert, ":cemail_bv", $cemail1);
	oci_execute($vInsert);
	oci_free_statement($vInsert);
}//end elseif
else
{
	oci_free_statement($vemailcheck);
	oci_free_statement($refercheck);
}//end else


$refercheck = oci_parse($conn, "select vemail from refer where vemail = :vemail_bv and cemail = :cemail_bv");
oci_bind_by_name($refercheck, ":vemail_bv", $vemail);
oci_bind_by_name($refercheck, ":cemail_bv", $cemail2);
oci_execute($refercheck);
$row = oci_fetch_array($refercheck);
$numRows1 = count($row);

if($cemail2 != NULL && $numRows1 == 1)
{
	oci_free_statement($refercheck);	
	$vInsert = oci_parse($conn, "insert into refer values(:cemail_bv, :vemail_bv)");
	oci_bind_by_name($vInsert, ":vemail_bv", $vemail);
	oci_bind_by_name($vInsert, ":cemail_bv", $cemail2);
	oci_execute($vInsert);
	oci_free_statement($vInsert);
}//end if

$refercheck = oci_parse($conn, "select vemail from refer where vemail = :vemail_bv and cemail = :cemail_bv");
oci_bind_by_name($refercheck, ":vemail_bv", $vemail);
oci_bind_by_name($refercheck, ":cemail_bv", $cemail3);
oci_execute($refercheck);
$row = oci_fetch_array($refercheck);
$numRows1 = count($row);

if($cemail3 != NULL && $numRows1 == 1)
{
	oci_free_statement($refercheck);	
	$vInsert = oci_parse($conn, "insert into refer values(:cemail_bv, :vemail_bv)");
	oci_bind_by_name($vInsert, ":vemail_bv", $vemail);
	oci_bind_by_name($vInsert, ":cemail_bv", $cemail3);
	oci_execute($vInsert);
	oci_free_statement($vInsert);
}//end if

$refercheck = oci_parse($conn, "select vemail from refer where vemail = :vemail_bv and cemail = :cemail_bv");
oci_bind_by_name($refercheck, ":vemail_bv", $vemail);
oci_bind_by_name($refercheck, ":cemail_bv", $cemail4);
oci_execute($refercheck);
$row = oci_fetch_array($refercheck);
$numRows1 = count($row);

if($cemail4 != NULL && $numRows1 == 1)
{
	oci_free_statement($refercheck);
	$vInsert = oci_parse($conn, "insert into refer values(:cemail_bv, :vemail_bv)");
	oci_bind_by_name($vInsert, ":vemail_bv", $vemail);
	oci_bind_by_name($vInsert, ":cemail_bv", $cemail4);
	oci_execute($vInsert);
	oci_free_statement($vInsert);
}//end if

$refercheck = oci_parse($conn, "select vemail from refer where vemail = :vemail_bv and cemail = :cemail_bv");
oci_bind_by_name($refercheck, ":vemail_bv", $vemail);
oci_bind_by_name($refercheck, ":cemail_bv", $cemail5);
oci_execute($refercheck);
$row = oci_fetch_array($refercheck);
$numRows1 = count($row);

if($cemail5 != NULL && $numRows1 == 1)
{
	oci_free_statement($refercheck);
	$vInsert = oci_parse($conn, "insert into refer values(:cemail_bv, :vemail_bv)");
	oci_bind_by_name($vInsert, ":vemail_bv", $vemail);
	oci_bind_by_name($vInsert, ":cemail_bv", $cemail5);
	oci_execute($vInsert);
	oci_free_statement($vInsert);
}//end if

oci_close($conn);
echo("<h2>Your information has been processed. Thank you!<h2>");
?>
</body></html>
